% Cohort Transprob cacluation with conditions:
% Rates {1+1.5, 2,2.5,3,3.5,4, 4.5+5+5.5, -1};
% maxPPDOS_sum constrains is controlled by variable 'conditions' in loop
% industry = ''Manufacture'', ''Retail'',''Wholesale''
% province = ''Quebec'' or ''Ontario'' 
% cdIdex
% yearly

clear all
clc

conn = database('BDC','','');
labels = {1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,-1};
edges = [2 3 4 5 6 7 10 11 12];
%edgesDur = [2 3 4 5 6 7 10 11];

year_list=1997:2009;
DURATION = 1;
NUMBER_OF_YEAR = length(year_list) - DURATION;
conditions = {'maxPPDOS_sum >= 50000','maxPPDOS_sum >= 50000 and maxPPDOS_sum < 250000',...
    'maxPPDOS_sum >= 250000 and maxPPDOS_sum <1000000','maxPPDOS_sum >= 1000000'};
numRate = length(labels)+1; % add column for WR group
numEdges = length(edges);
numEdgesStd = numEdges-1;
numGrp = length(conditions);

coh = zeros(numEdgesStd, numEdgesStd);
cohTp = zeros(numGrp, NUMBER_OF_YEAR, numRate, numRate);
cohTpGrp = zeros(numGrp, NUMBER_OF_YEAR, numEdges, numEdges);
cohTpGrpStd =zeros(numGrp, NUMBER_OF_YEAR, numEdgesStd, numEdgesStd);
cohTtlStdVec = zeros(numGrp, NUMBER_OF_YEAR, 1, numEdgesStd);
cohTtlStdMat = zeros(numGrp, NUMBER_OF_YEAR, numEdgesStd, numEdgesStd);
SVD = zeros(NUMBER_OF_YEAR,numGrp);
PD = zeros (NUMBER_OF_YEAR , numGrp);

for cdIdx = 1:numGrp
    disp(conditions(cdIdx));
    for yearIdx=1:NUMBER_OF_YEAR
        
        Date1 = strcat(num2str(year_list(yearIdx)),'-12-01');
        Date2 = strcat(num2str(year_list(yearIdx+DURATION)),'-12-01');
        disp( strcat(['Date: ' Date1 ' to ' Date2]));
        
        condition = char(conditions(cdIdx));
        sqlquery1 = strcat(['select Customer, modiFisDate, modiRate FROM final_v4_tbl where ' condition ' and industry = ''Manufacture'' and modiFisDate BETWEEN ''' Date1 ''' and ''' Date2 ''' order by customer, modiFisDate']);
        results = fetch(conn, sqlquery1);
        disp(size(results));
        
        % cohort
        % we cannot use function 'transprob' because it doesn't consider the WR situation.
        [cohTp(cdIdx,yearIdx,:,:),ttl] = transprobwr(results, 'labels',labels,'algorithm','cohort');
        
        TpGrp = transprobgrouptotals(ttl,edges);
        cohTpGrp(cdIdx,yearIdx,:,:) = transprobbytotalswr(TpGrp);
               
        TpGrpStd.totalsVec = sum(TpGrp.totalsMat(1:numEdgesStd,1:numEdgesStd),2)';
        TpGrpStd.totalsMat = TpGrp.totalsMat(1:numEdgesStd,1:numEdgesStd);
        TpGrpStd.algorithm = TpGrp.algorithm;
        cohTpGrpStd(cdIdx,yearIdx,:,:) = transprobbytotals(TpGrpStd);
        cohTtlStdVec (cdIdx,yearIdx,1,:) = TpGrpStd.totalsVec;
        cohTtlStdMat (cdIdx,yearIdx,:,:) = TpGrpStd.totalsMat;  
        disp('Cohort tranprob is done')  
        
        % SVD & PD
        coh (:,:) =  cohTpGrpStd(cdIdx, yearIdx, :,:)./100;
        SVD(yearIdx, cdIdx) = mean(svd(coh-eye(numEdgesStd)));
        Mat(:,:) = cohTtlStdMat (cdIdx,yearIdx,:,:);
        PD(yearIdx, cdIdx)= sum(Mat(:,numEdgesStd)) / sum(sum(Mat));
            
        disp('SVD & PD is done')  
        
    end
end

save ('D:\My Documents\CIRANO\db_mySQL\tp_yr_sector_manufacture.mat')


% figure SVD & PD

Y = cellstr(char('1998','1999','2000','2001','2002','2003','2004','2005','2006','2007','2008','2009'));
R = cellstr(char('1.5','2','2.5','3','3.5','4','4.5'));
SG = cellstr(char('Total sample: [$50k,+)','Subsample A: [$50k, 250k)','Subsample B: [$250k, 1000k)','Subsample C: [$1000k, +)'));


figure;
plot (PD);
set(gca,'XTick',1:1:NUMBER_OF_YEAR);
set(gca,'XTickLabel',Y);
grid on;
xlabel('Years');
ylabel('Default Frequency');
legend(SG,'Location','NorthWest');

figure;
plot (SVD);
set(gca,'XTick',1:1:NUMBER_OF_YEAR);
set(gca,'XTickLabel',Y);
grid on;
xlabel('Years');
ylabel('SVD');
legend(SG,'Location','NorthWest');
